/***
This do-file creates the data used to create maps of changes in small 
business revenue by ZIP code. 
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
include "${root}/code/set_globals.do"

* Create required subfolders
cap mkdir "${root}/data/derived/Small Business Revenue"
cap mkdir "${root}/results/Small Business Revenue"

*-------------------------------------------------------------------------------
* Get ZIP-level data from Womply pipeline
*-------------------------------------------------------------------------------
project, uses("${root}/data/web/data/Womply - ZCTA - 2020.csv")
import delimited "${root}/data/web/data/Womply - ZCTA - 2020.csv", clear

*-------------------------------------------------------------------------------
* Only keep ZCTAs in cities we want to map
*-------------------------------------------------------------------------------

* Create a crosswalk with the CBSAs we want (NYC, Chicago, SF, Nashville)
preserve
	project, uses("${root}/data/dvc/Crosswalks/zcta_cbsa_rel_10.txt")
	import delimited "${root}/data/dvc/Crosswalks/zcta_cbsa_rel_10.txt", clear
	rename zcta5 zcta
	keep zcta cbsa
	keep if inlist(cbsa, 16980, 35620, 41860)
	gen cbsa_name = "Chicago-Naperville-Elgin, IL-IN-WI" if cbsa == 16980
	replace cbsa_name = "New York-Newark-Jersey City, NY-NJ-PA" if cbsa == 35620
	replace cbsa_name = "San Francisco-Oakland-Hayward, CA" if cbsa == 41860

	tempfile zcta_cbsa
	save `zcta_cbsa'
restore

* Merge in CBSAs for finding the new areas
merge 1:1 zcta using `zcta_cbsa', keep(1 3) nogen keepusing(cbsa cbsa_name)

replace cbsa_name = "New York City" if cbsa_name == "New York-Newark-Jersey City, NY-NJ-PA"
replace cbsa_name = "Chicago" if cbsa_name == "Chicago-Naperville-Elgin, IL-IN-WI"
replace cbsa_name = "San Francisco"  if cbsa_name == "San Francisco-Oakland-Hayward, CA"
keep if !mi(cbsa_name)

* Select the ZCTAs to retain
gen city = ""

foreach city in "Chicago" "New York City" "San Francisco" {

	* Get the right CBSAs 
	levelsof cbsa if cbsa_name == "`city'", local(cbsas)
	local correct_cbsas = subinstr("`cbsas'", " ", ", ", .)
	noi di `correct_cbsas'

	* Mark the ZCTAs to expand
	gen mark_ind = inlist(cbsa, `correct_cbsas')
	
	* Create the city obs
	expand 2 if mark_ind == 1, gen(expand_ind)
	replace city = "`city'" if expand_ind == 1
	drop expand_ind mark_ind
}

keep if inlist(city, "Chicago","New York City", "San Francisco")

gcollapse (mean) revenue_all_apr2020, by(zcta city)

gen pct_change_all = revenue_all_apr2020 * 100

*-------------------------------------------------------------------------------
* Export files
*-------------------------------------------------------------------------------

* Adapt the structure	
rename city cityname

* Keep the right variables
keep zcta cityname pct_change_all
drop if pct_change_all == .

* Export
export delimited "${root}/data/derived/Small Business Revenue/Map - Change in Small Business Revenue, ZIP.csv", replace
project, creates("${root}/data/derived/Small Business Revenue/Map - Change in Small Business Revenue, ZIP.csv")